package com.example.repository;

import com.example.model.Announcement;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 公告Repository
 */
@Repository
public interface AnnouncementRepository extends JpaRepository<Announcement, Long> {

    /**
     * 查找已发布的公告
     */
    List<Announcement> findByStatusOrderByCreatedAtDesc(Announcement.PublishStatus status);

    /**
     * 分页查找已发布的公告
     */
    Page<Announcement> findByStatusOrderByCreatedAtDesc(Announcement.PublishStatus status, Pageable pageable);

    /**
     * 根据目标类型查找已发布的公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = :status AND " +
           "(a.targetType = :targetType OR a.targetType = 'ALL') " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    List<Announcement> findByStatusAndTargetType(@Param("status") Announcement.PublishStatus status,
                                                @Param("targetType") Announcement.TargetType targetType);

    /**
     * 分页根据目标类型查找已发布的公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = :status AND " +
           "(a.targetType = :targetType OR a.targetType = 'ALL') " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    Page<Announcement> findByStatusAndTargetType(@Param("status") Announcement.PublishStatus status,
                                               @Param("targetType") Announcement.TargetType targetType,
                                               Pageable pageable);

    /**
     * 查找有效的公告（已发布且未过期）
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND " +
           "(a.expiresAt IS NULL OR a.expiresAt > :now) " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    List<Announcement> findValidAnnouncements(@Param("now") LocalDateTime now);

    /**
     * 根据目标类型查找有效的公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND " +
           "(a.expiresAt IS NULL OR a.expiresAt > :now) AND " +
           "(a.targetType = :targetType OR a.targetType = 'ALL') " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    List<Announcement> findValidAnnouncementsByTargetType(@Param("targetType") Announcement.TargetType targetType,
                                                         @Param("now") LocalDateTime now);

    /**
     * 查找置顶公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND a.isPinned = true AND " +
           "(a.expiresAt IS NULL OR a.expiresAt > :now) " +
           "ORDER BY a.priority DESC, a.createdAt DESC")
    List<Announcement> findPinnedAnnouncements(@Param("now") LocalDateTime now);

    /**
     * 根据优先级查找公告
     */
    List<Announcement> findByPriorityAndStatusOrderByCreatedAtDesc(Announcement.Priority priority, 
                                                                  Announcement.PublishStatus status);

    /**
     * 查找需要发布的公告（计划发布时间已到）
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'SCHEDULED' AND a.scheduledAt <= :now")
    List<Announcement> findScheduledAnnouncementsToPublish(@Param("now") LocalDateTime now);

    /**
     * 查找已过期的公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND a.expiresAt IS NOT NULL AND a.expiresAt <= :now")
    List<Announcement> findExpiredAnnouncements(@Param("now") LocalDateTime now);

    /**
     * 根据发布者查找公告
     */
    List<Announcement> findByPublisherIdOrderByCreatedAtDesc(String publisherId);

    /**
     * 根据公告类型查找公告
     */
    List<Announcement> findByTypeAndStatusOrderByCreatedAtDesc(Announcement.AnnouncementType type, 
                                                              Announcement.PublishStatus status);

    /**
     * 搜索公告（标题或内容包含关键词）
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND " +
           "(LOWER(a.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(a.content) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    List<Announcement> searchAnnouncements(@Param("keyword") String keyword);

    /**
     * 分页搜索公告
     */
    @Query("SELECT a FROM Announcement a WHERE a.status = 'PUBLISHED' AND " +
           "(LOWER(a.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(a.content) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "ORDER BY a.isPinned DESC, a.priority DESC, a.createdAt DESC")
    Page<Announcement> searchAnnouncements(@Param("keyword") String keyword, Pageable pageable);

    /**
     * 增加阅读次数
     */
    @Modifying
    @Query("UPDATE Announcement a SET a.readCount = a.readCount + 1 WHERE a.id = :id")
    void incrementReadCount(@Param("id") Long id);

    /**
     * 批量更新过期公告状态
     */
    @Modifying
    @Query("UPDATE Announcement a SET a.status = 'EXPIRED' WHERE a.status = 'PUBLISHED' AND a.expiresAt IS NOT NULL AND a.expiresAt <= :now")
    int updateExpiredAnnouncements(@Param("now") LocalDateTime now);

    /**
     * 批量发布计划公告
     */
    @Modifying
    @Query("UPDATE Announcement a SET a.status = 'PUBLISHED', a.publishedAt = :now WHERE a.status = 'SCHEDULED' AND a.scheduledAt <= :now")
    int publishScheduledAnnouncements(@Param("now") LocalDateTime now);

    /**
     * 统计各状态公告数量
     */
    @Query("SELECT a.status, COUNT(a) FROM Announcement a GROUP BY a.status")
    List<Object[]> countByStatus();

    /**
     * 统计各类型公告数量
     */
    @Query("SELECT a.type, COUNT(a) FROM Announcement a WHERE a.status = 'PUBLISHED' GROUP BY a.type")
    List<Object[]> countByType();
}
